﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace ManagementProject
{
    public partial class frmProduct : Form
    {
        SqlConnection conn;
        SqlDataAdapter da;
        DataSet ds;
        DataTable dt;
        DataView dv;
        SqlCommandBuilder cb;
        string error = null;
        string ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        public frmProduct()
        {
            InitializeComponent();
            creatConnectionToDB();
            loadProductsFromDB();
            loadDataIntoDgdvProuct();
            loadItemsCboCategoryName();
            loadItemsCboSupplierName();
            loadItemsCboSearchBy();
            clearProductDetail();
            timerClearStatus.Start();
        }
        void creatConnectionToDB()
        {
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
                error += "\n";
                error += ex.Message;
                lblStatus.Text = "Wrong connectionString";
                conn.Close();
            }

        }
        void loadProductsFromDB()
        {
            creatConnectionToDB();
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Production.getProducts";
                da = new SqlDataAdapter(cmd);
                cb = new SqlCommandBuilder(da);
                ds = new DataSet();
                da.FillSchema(ds, SchemaType.Mapped);
                da.Fill(ds, "Products");                
            }
            catch (System.Exception ex)
            {
                error += "\n";
                error += ex.Message;
                lblStatus.Text = "Load database failed!";
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
           
            #region command
            //#region LoadDataInDataGridView
            ////cmd.CommandText = "SELECT p.productid, p.productname, c.categoryname, s.companyname, p.unitprice, p.discontinued, c.categoryid, s.supplierid "
            ////                   + "FROM Production.Products p, Production.Categories c, Production.Suppliers s "
            ////                   + "WHERE p.categoryid = c.categoryid AND p.supplierid = s.supplierid";
            ////SqlDataReader dr = cmd.ExecuteReader();
            ////while (dr.Read())
            ////{
            ////    dgdvProuct.Rows.Add(dr["productid"].ToString(), dr["productname"].ToString(),
            ////                        dr["categoryname"].ToString(), dr["companyname"].ToString(),
            ////                        dr["unitprice"].ToString(), dr["discontinued"].ToString(),
            ////                        dr["categoryid"].ToString(), dr["supplierid"].ToString());
            ////}
            ////dr.Close();
            //#endregion

            //#region loadItemsForCboCategoryName
            //cmd.CommandText = "SELECT categoryname "
            //                  + "FROM Production.Categories "
            //                  + "GROUP BY categoryname";
            //dr = cmd.ExecuteReader();
            //List<string> listCategoryName = new List<string>();
            //while (dr.Read())
            //{
            //    listCategoryName.Add(dr[0].ToString());
            //}
            //cboCategoryName.DataSource = listCategoryName;
            //dr.Close();
            //#endregion

            //#region loadItemsForCboSupplier
            //cmd.CommandText = "SELECT companyname "
            //                   + "FROM Production.Suppliers "
            //                   + "GROUP BY companyname";
            //dr = cmd.ExecuteReader();
            //List<string> listSupplierName = new List<string>();
            //while (dr.Read())
            //{
            //    listSupplierName.Add(dr[0].ToString());
            //}
            //cboSupplierName.DataSource = listSupplierName;
            //dr.Close();
            //#endregion

            //#region loadItemsForCboSearchBy
            //List<string> listItemsSearch = new List<string>();
            //listItemsSearch.Add("ID");
            //listItemsSearch.Add("Product Name");
            //listItemsSearch.Add("Category Name");
            //listItemsSearch.Add("Supplier Name");
            //listItemsSearch.Add("Price");
            //listItemsSearch.Add("DisContinued");
            //listItemsSearch.Add("Category ID");
            //listItemsSearch.Add("Supplier ID");
            //cboSearchBy.DataSource = listItemsSearch;
            //#endregion
            #endregion
            
        }
        void loadDataIntoDgdvProuct() 
        {
            try
            {
                dt = ds.Tables["Products"];
                dv = new DataView(dt);
                dgdvProuct.DataSource = dt;
                dgdvProuct.ClearSelection();
            }
            catch (System.Exception ex)
            {
            	lblStatus.Text="Load DB into GirdView failed";
            }
            
        }
        void loadItemsCboCategoryName() 
        {
            creatConnectionToDB();                        
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Production.getCategoriesName";
                da = new SqlDataAdapter(cmd);
                cb = new SqlCommandBuilder(da);
                ds = new DataSet();
                DataTable dt = new DataTable("Production.CategoriesName");
                da.Fill(dt);
                cboCategoryName.DataSource = dt;
                cboCategoryName.DisplayMember = "categoryname";
                cboCategoryName.ValueMember = "categoryid";                
                cboCategoryName.SelectedIndex = -1;
            }
            catch (System.Exception ex)
            {
                error += "\n";
                error += ex.Message;
                lblStatus.Text = "Load category name items failed!";
            }
            finally
            {
                conn.Close();
            }
        }
        void loadItemsCboSupplierName()
        {
            creatConnectionToDB();
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Production.getSuppliersName";
                da = new SqlDataAdapter(cmd);
                cb = new SqlCommandBuilder(da);
                ds = new DataSet();
                DataTable dt = new DataTable("Categories");
                da.Fill(dt);
                cboSupplierName.DataSource = dt;
                cboSupplierName.DisplayMember = "companyname";
                cboSupplierName.ValueMember = "supplierid";
                cboSupplierName.SelectedIndex = -1;
            }
            catch (System.Exception ex)
            {
                error += "\n";
                error += ex.Message;
                lblStatus.Text = "Load supplier name items failed!";
            }
            finally
            {
                conn.Close();
            }
        }
        void loadItemsCboSearchBy()
        {
            List<string> list = new List<string>();
            list.Add("ID");
            list.Add("Product Name");
            list.Add("Category Name");
            list.Add("Supplier Name");
            list.Add("Price");
            list.Add("DisContinued");
            list.Add("Category ID");
            list.Add("Supplier ID");
            cboSearchBy.DataSource = list;

        }

        void clearProductDetail()
        {
            txtProductID.Text = null;
            txtProductName.Text = null;
            cboSupplierName.SelectedIndex = -1;
            cboCategoryName.SelectedIndex = -1;
            txtUnitPrice.Text = null;
            chkDiscontinued.Checked = false;
            btnAdd.Enabled = true;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            txtSearch.Text = null;
        }
        void disableAll() 
        {
            txtProductName.Enabled = false;
            cboCategoryName.Enabled = false;
            cboSupplierName.Enabled = false;
            txtUnitPrice.Enabled = false;
            chkDiscontinued.Enabled = false;
            btnAdd.Enabled = false;
            btnNew.Enabled = false;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            btnSearch.Enabled = false;
        }
        bool validateInput()
        {
            string productName = txtProductName.Text.Trim();
            string categoryName = cboCategoryName.Text.Trim();
            string supplierName = cboSupplierName.Text.Trim();
            double price;
            bool error = false;
            if (productName.Length == 0)
            {
                errorInput.SetError(txtProductName, "Please enter product's name");
                error = true;
            }
            if (productName.Length > 40)
            {
                errorInput.SetError(txtProductName,"Product name too long!");
                error = true;
            }
            if (categoryName.Length == 0)
            {
                errorInput.SetError(cboCategoryName, "Please select category");
                error = true;
            }
            if (supplierName.Length == 0)
            {
                errorInput.SetError(cboSupplierName, "Please select supplier");
                error = true;
            }
            try
            {
                price = Double.Parse(txtUnitPrice.Text);
                if (price<0)
                {
                    error = true;
                    errorInput.SetError(txtUnitPrice, "Price must be positive");
                }
            }
            catch (System.Exception ex)
            {
                errorInput.SetError(txtUnitPrice, "Price must be double");
                error = true;
            }
            if (error == true)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        void addProducts()
        {
            if (validateInput())
            {
                try
                {
                    creatConnectionToDB();
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Production.insertProducts";

                    SqlParameter param = new SqlParameter("@productname", SqlDbType.NVarChar);
                    param.Size = 40;
                    param.Value = txtProductName.Text.Trim().ToString();
                    cmd.Parameters.Add(param);                    

                    param = new SqlParameter("@supplierid", SqlDbType.Int);
                    param.Value = cboSupplierName.SelectedValue;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@categoryid", SqlDbType.Int);
                    param.Value = cboCategoryName.SelectedValue;
                    cmd.Parameters.Add(param);


                    param = new SqlParameter("@unitprice", SqlDbType.Money);
                    param.Value = Double.Parse(txtUnitPrice.Text.Trim().ToString());
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@discontinued", SqlDbType.Bit);
                    param.Value = chkDiscontinued.Checked;
                    cmd.Parameters.Add(param);


                    cmd.ExecuteNonQuery();
                    conn.Close();
                    clearProductDetail();
                    loadProductsFromDB();
                    loadDataIntoDgdvProuct();
                    loadItemsCboCategoryName();
                    loadItemsCboSupplierName();
                    lblStatus.Text = "Add successful!";
                }
                catch (System.Exception ex)
                {
                    lblStatus.Text = "Add failed!";
                    error += "\n";
                    error += ex.Message;
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
            
        }
        void updateProducts() 
        {
            if (validateInput())
            {                
                DialogResult confirm = MessageBox.Show("Are you sure?", "Update Prodcuts", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (confirm == DialogResult.Yes)
                {
                    creatConnectionToDB();
                    try
                    {
                        conn.Open();
                        SqlCommand cmd = new SqlCommand();
                        cmd.Connection = conn;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "Production.updateProducts";

                        SqlParameter param = new SqlParameter("@productid", SqlDbType.Int);
                        param.Value = int.Parse(txtProductID.Text);
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@productname", SqlDbType.NVarChar);
                        param.Size = 40;
                        param.Value = txtProductName.Text.Trim().ToString();
                        cmd.Parameters.Add(param);                      

                        param = new SqlParameter("@supplierid", SqlDbType.Int);
                        param.Value = cboSupplierName.SelectedValue;
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@categoryid", SqlDbType.Int);
                        param.Value = cboCategoryName.SelectedValue;
                        cmd.Parameters.Add(param);


                        param = new SqlParameter("@unitprice", SqlDbType.Money);
                        param.Value = Double.Parse(txtUnitPrice.Text.Trim().ToString());
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@discontinued", SqlDbType.Bit);
                        param.Value = chkDiscontinued.Checked;
                        cmd.Parameters.Add(param);


                        cmd.ExecuteNonQuery();
                        conn.Close();
                        clearProductDetail();
                        loadProductsFromDB();
                        loadDataIntoDgdvProuct();
                        loadItemsCboCategoryName();
                        loadItemsCboSupplierName();
                        lblStatus.Text = "Update successful!";
                        btnAdd.Enabled = true;
                        btnUpdate.Enabled = false;
                        btnDelete.Enabled = false;
                    }
                    catch (System.Exception ex)
                    {
                        lblStatus.Text = "Update failed!";
                        error += "\n";
                        error += ex.Message;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
        void deleteProducts()
        {           
            DialogResult result = MessageBox.Show("Are you sure?", "Delete Products", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (result == DialogResult.Yes)
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Production.delProducts";
                    SqlParameter param = new SqlParameter("@productid", SqlDbType.Int);
                    param.Value = int.Parse(txtProductID.Text);
                    cmd.Parameters.Add(param);
                    cmd.ExecuteNonQuery();
                    clearProductDetail();
                    loadProductsFromDB();
                    loadDataIntoDgdvProuct();
                    loadItemsCboCategoryName();
                    loadItemsCboSupplierName();
                    lblStatus.Text = "Delete successfull";
                }
                catch (System.Exception ex)
                {
                    lblStatus.Text = "Delete fail!";
                    error += "\n";
                    error += ex.Message;
                    MessageBox.Show(string.Format("May it's in using by another then you can't remove it \n Support: chanhvqse61295@fpt.edu.vn"),
                                "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                finally
                {
                    conn.Close();
                    clearProductDetail();
                }
            }
        }
        void searchProducts()
        {
            try
            {
                if (cboSearchBy.Text.Length>0)
                {
                    if (txtSearch.Text.Length > 0)
                    {
                        string searchBy = cboSearchBy.Text.Trim();

                        if (searchBy.Equals("ID"))
                        {
                            try
                            {
                                dv = new DataView(dt);
                                dv.RowFilter = string.Format("[Product ID] = {0}", int.Parse(txtSearch.Text.Trim()));
                                dgdvProuct.DataSource = dv;
                                dgdvProuct.ClearSelection();
                                lblStatus.Text = "Search successful!";
                            }
                            catch (System.Exception ex)
                            {
                                errorInput.SetError(txtSearch, "ID must be number!");
                            }

                        }
                        if (searchBy.Equals("Product Name"))
                        {
                            dv = new DataView(dt);
                            dv.RowFilter = string.Format("[Product Name] LIKE '%{0}%'", txtSearch.Text.Trim());
                            dgdvProuct.DataSource = dv;
                            dgdvProuct.ClearSelection();
                            lblStatus.Text = "Search successful!";
                        }
                        if (searchBy.Equals("Category Name"))
                        {
                            dv = new DataView(dt);
                            dv.RowFilter = string.Format("[Category Name] LIKE '%{0}%'", txtSearch.Text.Trim());
                            dgdvProuct.DataSource = dv;
                            dgdvProuct.ClearSelection();
                            lblStatus.Text = "Search successful!";
                        }
                        if (searchBy.Equals("Supplier Name"))
                        {
                            dv = new DataView(dt);
                            dv.RowFilter = string.Format("[Suplier Name] LIKE '%{0}%'", txtSearch.Text.Trim());
                            dgdvProuct.DataSource = dv;
                            dgdvProuct.ClearSelection();
                            lblStatus.Text = "Search successful!";
                        }
                        if (searchBy.Equals("Price"))
                        {
                            try
                            {
                                dv = new DataView(dt);
                                dv.RowFilter = string.Format("[Unit Price] = {0}", Double.Parse(txtSearch.Text));
                                dgdvProuct.DataSource = dv;
                                dgdvProuct.ClearSelection();
                                lblStatus.Text = "Search successful!";
                                errorInput.Clear();
                            }
                            catch (System.Exception ex)
                            {
                                errorInput.SetError(txtSearch, "Price must be double");
                                error += "\n";
                                error += ex.Message;
                            }

                        }
                        if (searchBy.Equals("DisContinued"))
                        {
                            if (txtSearch.Text.Trim().Length > 0)
                            {
                                dv = new DataView(dt);
                                dv.RowFilter = string.Format("[Discontinued] = {0}", txtSearch.Text.Trim());
                                dgdvProuct.DataSource = dv;
                                dgdvProuct.ClearSelection();
                                lblStatus.Text = "Search successful!";
                            }
                            chksearchDis.Visible = false;
                            txtSearch.Enabled = true;

                        }
                        if (searchBy.Equals("Category ID"))
                        {
                            try
                            {
                                dv = new DataView(dt);
                                dv.RowFilter = string.Format("[Category ID] = {0}", int.Parse(txtSearch.Text));
                                dgdvProuct.DataSource = dv;
                                dgdvProuct.ClearSelection();
                                lblStatus.Text = "Search successful!";
                            }
                            catch (System.Exception ex)
                            {
                                errorInput.SetError(txtSearch, "Value must be number");
                            }
                            

                        }
                        if (searchBy.Equals("Supplier ID"))
                        {
                            try
                            {
                                dv = new DataView(dt);
                                dv.RowFilter = string.Format("[Supplier ID] = {0}", int.Parse(txtSearch.Text));
                                dgdvProuct.DataSource = dv;
                                dgdvProuct.ClearSelection();
                                lblStatus.Text = "Search successful!";
                            }
                            catch (System.Exception ex)
                            {
                                errorInput.SetError(txtSearch, "value must be number");
                            }
                            
                        }
                        txtSearch.Text = null;
                    }
                    else
                    {
                        errorInput.SetError(txtSearch, "Pleas enter value");
                        clearProductDetail();
                    }
                }
                else
                {
                    errorInput.SetError(cboSearchBy,"Please select value");
                }
                
            }
            catch (System.Exception ex)
            {
                lblStatus.Text = "Search failed";
                error += "\n";
                error += ex.Message;
                MessageBox.Show(ex.Message);
            }
            finally
            {
                txtSearch.Text = null;
                clearProductDetail();
                btnAdd.Enabled = true;
                btnNew.Enabled = true;
                btnUpdate.Enabled = false;
                btnDelete.Enabled = false;
            }
            
            
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            clearProductDetail();
            loadProductsFromDB();
            loadDataIntoDgdvProuct();
            loadItemsCboCategoryName();
            loadItemsCboSupplierName();
            errorInput.Clear();            
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {            
            addProducts();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            updateProducts();
                        
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            deleteProducts();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            searchProducts();
        }     

        private void btnReload_Click(object sender, EventArgs e)
        {
            try
            {
                loadProductsFromDB();
                loadDataIntoDgdvProuct();
                loadItemsCboCategoryName();
                loadItemsCboSupplierName();
                lblStatus.Text = "Reload successful!";
            }
            catch (System.Exception ex)
            {
                lblStatus.Text = "Reload failed";
                error += "\n";
                error += ex.Message;
            }
        }          

        private void dgdvProuct_SelectionChanged(object sender, EventArgs e)
        {
            if (dgdvProuct.SelectedRows.Count > 0)
            {
                btnAdd.Enabled = false;
                btnUpdate.Enabled = true;
                btnDelete.Enabled = true;
                DataGridViewRow r = dgdvProuct.SelectedRows[0];
                txtProductID.Text = r.Cells[0].Value.ToString();
                txtProductName.Text = r.Cells[1].Value.ToString();
                int supplierNameValue = r.Cells[2].Value.GetHashCode();
                cboSupplierName.SelectedValue = supplierNameValue;
                int categoryNameValue = r.Cells[3].Value.GetHashCode();
                cboCategoryName.SelectedValue = categoryNameValue;
                txtUnitPrice.Text = r.Cells[4].Value.ToString();

                string discontinued = r.Cells[5].Value.ToString();
                if (discontinued == "True")
                {
                    chkDiscontinued.Checked = true;
                }
                else
                {
                    chkDiscontinued.Checked = false;
                }

            }
        }

        private void cboSearchBy_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cboSearchBy.Text.Equals("DisContinued"))
            {
                txtSearch.Enabled = false;
                chksearchDis.Visible = true;
                if (chksearchDis.Checked == true)
                {
                    txtSearch.Text = "True";
                }
                else
                {
                    txtSearch.Text = "False";
                } 
            }
            else
            {
                txtSearch.Text = null;
                txtSearch.Enabled = true;
                chksearchDis.Visible = false;
            }
            
        }

        private void chksearchDis_CheckedChanged(object sender, EventArgs e)
        {
            if (chksearchDis.Checked == true)
            {
                txtSearch.Text = "True";
            }
            else
            {
                txtSearch.Text = "False";
            }
        }

        private void timerClearStatus_Tick(object sender, EventArgs e)
        {
            lblStatus.Text = null;
            errorInput.Clear();
        }
    }
}
